/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pv168.uloha3.managers.impl;

import cz.muni.fi.pv168.uloha3.common.DBUtils;
import cz.muni.fi.pv168.uloha3.entities.Owner;
import cz.muni.fi.pv168.uloha3.exceptions.OwnerManagerException;
import cz.muni.fi.pv168.uloha3.managers.OwnerManager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author stefan
 */
public class OwnerManagerImpl implements OwnerManager {
    
    private static final Logger logger = Logger.getLogger(OwnerManagerImpl.class.getName());
    
    private DataSource dataSource;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private void checkDataSource() {
        if (dataSource == null) {
            throw new IllegalStateException("DataSource is not set");
        }
    }
    
    static {
        try {
            OwnerManagerImpl.class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException ex) {
            // what to do
        }
    }
    
    private static final String URL = "jdbc:derby://localhost:1527/pv168";
    private static final String USERNAME = "uzivatel";
    private static final String PASSWORD = "heslo";
    
    private static final String PREPARED_STATEMENT_ADD_OWNER_STR = "INSERT INTO OWNER (SSN, FIRST_NAME, LAST_NAME, ADDRESS, TELEPHONE) VALUES (?, ?, ?, ?, ?)";
    private static final String PREPARED_STATEMENT_EDIT_OWNER_STR = "UPDATE OWNER SET SSN=?, FIRST_NAME=?, LAST_NAME=?, ADDRESS=?, TELEPHONE=? WHERE ID=?";
    private static final String PREPARED_STATEMENT_GET_OWNERS_STR = "SELECT ID, SSN, FIRST_NAME, LAST_NAME, ADDRESS, TELEPHONE FROM OWNER";
    
    /* TODO: construct this String */
    private static final String PREPARED_STATEMENT_DELETE_OWNER_STR = "DELETE FROM OWNER WHERE ID=?";  
    private static final String PREPARED_STATEMENT_GET_OWNER_BY_ID_STR = "SELECT ID, SSN, FIRST_NAME, LAST_NAME, ADDRESS, TELEPHONE FROM OWNER WHERE ID=?";  

    public void addOwner(Owner owner) throws OwnerManagerException {
        checkDataSource();
        if(null == owner) {
            throw new OwnerManagerException("Provided owner cannot be null");
            
        } else if(null == owner.getSsn() || owner.getSsn().isEmpty() 
                    || null == owner.getFirstName() || owner.getFirstName().isEmpty()
                    || null == owner.getLastName() || owner.getLastName().isEmpty()) {
            throw new OwnerManagerException("Following Owner fields cannot be null: ssn, firstName nor lastName");
        }
        
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_ADD_OWNER_STR, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, owner.getSsn());
            preparedStatement.setString(2, owner.getFirstName());
            preparedStatement.setString(3, owner.getLastName());
            preparedStatement.setString(4, owner.getAddress());
            preparedStatement.setString(5, owner.getTelephone());
            
            preparedStatement.executeUpdate();
            
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if(generatedKeys.next()) {
                Long ownerId = generatedKeys.getLong(1);
                owner.setId(ownerId);
            } else {
                throw new OwnerManagerException("No primary key generated for new Owner " + owner);
            }
            
            connection.commit();
        } catch (SQLException ex) {
            String msg = "Error when adding owner into db";
            logger.log(Level.SEVERE, msg, ex);
            throw new OwnerManagerException("SQLException caught while trying to add Owner " + owner + " to database: " + ex.getMessage(), ex);
        } finally {
            DBUtils.doRollbackQuietly(connection);
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
    }

    public void editOwner(Owner owner) throws OwnerManagerException {
        checkDataSource();
        if(null == owner) {
            throw new OwnerManagerException("Provided owner cannot be null");
            
        } else if(null == owner.getId() 
                    || null == owner.getSsn() || owner.getSsn().isEmpty() 
                    || null == owner.getFirstName() || owner.getFirstName().isEmpty()
                    || null == owner.getLastName() || owner.getLastName().isEmpty()) {
            throw new OwnerManagerException("Following Owner fields cannot be null: id, ssn, firstName nor lastName");
        }
        
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_EDIT_OWNER_STR);
            preparedStatement.setString(1, owner.getSsn());
            preparedStatement.setString(2, owner.getFirstName());
            preparedStatement.setString(3, owner.getLastName());
            preparedStatement.setString(4, owner.getAddress());
            preparedStatement.setString(5, owner.getTelephone());
            preparedStatement.setLong(6, owner.getId());
            
            preparedStatement.executeUpdate();
            
            connection.commit();
        } catch (SQLException ex) {
            String msg = "Error when updating owner in the db";
            logger.log(Level.SEVERE, msg, ex);
            throw new OwnerManagerException("SQLException caught while trying to edit Owner " + owner + ": " + ex.getMessage(), ex);
        } finally {
            DBUtils.doRollbackQuietly(connection);
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
    }

    public void deleteOwner(Owner owner) throws OwnerManagerException {
        checkDataSource();
        if(null == owner) {
            throw new OwnerManagerException("Provided owner cannot be null");
            
        } else if(null == owner.getId()) {
            throw new OwnerManagerException("Following Owner fields cannot be null: id, ssn, firstName nor lastName");
        }
        
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_DELETE_OWNER_STR);
            preparedStatement.setLong(1, owner.getId());
            
            preparedStatement.executeUpdate();
            
            connection.commit();
        } catch (SQLException ex) {
            String msg = "Error when deleting owner from the db";
            logger.log(Level.SEVERE, msg, ex);
            throw new OwnerManagerException("SQLException caught while trying to delete Owner " + owner + ": " + ex.getMessage(), ex);
        } finally {
            DBUtils.doRollbackQuietly(connection);
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
    }

    public Set<Owner> getOwners() throws OwnerManagerException {
        checkDataSource();
        Set<Owner> result = new HashSet<Owner>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_OWNERS_STR);
            
            ResultSet resultSet = preparedStatement.executeQuery();
            Owner owner;
            
            while(resultSet.next()) {
                owner = new Owner();
                owner.setId(resultSet.getLong(1));
                owner.setSsn(resultSet.getString(2));
                owner.setFirstName(resultSet.getString(3));
                owner.setLastName(resultSet.getString(4));
                owner.setAddress(resultSet.getString(5));
                owner.setTelephone(resultSet.getString(6));
                result.add(owner);
            }
            
        } catch (SQLException ex) {
            String msg = "Error when getting all owners from DB";
            logger.log(Level.SEVERE, msg, ex);
            throw new OwnerManagerException("SQLException caught while trying to get Owners: " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
        
        return result;
    }

    public Owner getOwnerById(Long id) throws OwnerManagerException {
        checkDataSource();
        
        if (id == null) {
            throw new IllegalArgumentException("id is null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        Owner owner = null;
        
        try {
            connection = dataSource.getConnection(); //DriverManager.getConnection(URL, USERNAME, PASSWORD);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_OWNER_BY_ID_STR);
            preparedStatement.setLong(1, id);
            
            ResultSet resultSet = preparedStatement.executeQuery();
            
            while(resultSet.next()) {
                owner = new Owner();
                owner.setId(resultSet.getLong(1));
                owner.setSsn(resultSet.getString(2));
                owner.setFirstName(resultSet.getString(3));
                owner.setLastName(resultSet.getString(4));
                owner.setAddress(resultSet.getString(5));
                owner.setTelephone(resultSet.getString(6));
            }
            
        } catch (SQLException ex) {
            String msg = "Error when getting owner with id = " + id + " from DB";
            logger.log(Level.SEVERE, msg, ex);
            throw new OwnerManagerException("SQLException caught while trying to get Owner: " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
        
        return owner;
    }
    
}
